package com.library.dao;

// BookDAO.java - 修改后的版本（解决Date冲突）

import com.library.entity.Book;
import com.library.util.DBUtil;
import java.sql.*;  // 保留sql包的导入
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class BookDAO {

    /**
     * 获取所有图书
     */
    public List<Book> getAllBooks() {
        List<Book> books = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT isbn, title, author, publisher, publish_date, price, " +
                    "category_id, location, total_copies, available_copies, status " +
                    "FROM books ORDER BY title";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Book book = resultSetToBook(rs);
                books.add(book);
            }
            System.out.println("从数据库加载 " + books.size() + " 本图书");
        } catch (SQLException e) {
            System.err.println("获取所有图书失败: " + e.getMessage());
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return books;
    }

    /**
     * 根据ISBN获取图书
     */
    public Book getBookByIsbn(String isbn) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT isbn, title, author, publisher, publish_date, price, " +
                    "category_id, location, total_copies, available_copies, status " +
                    "FROM books WHERE isbn = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, isbn);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return resultSetToBook(rs);
            }
        } catch (SQLException e) {
            System.err.println("根据ISBN获取图书失败: " + e.getMessage());
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return null;
    }

    /**
     * 搜索图书
     */
    public List<Book> searchBooks(String keyword) {
        List<Book> books = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT isbn, title, author, publisher, publish_date, price, " +
                    "category_id, location, total_copies, available_copies, status " +
                    "FROM books WHERE title LIKE ? OR author LIKE ? OR isbn LIKE ? " +
                    "ORDER BY title";
            pstmt = conn.prepareStatement(sql);
            String likeKeyword = "%" + keyword + "%";
            pstmt.setString(1, likeKeyword);
            pstmt.setString(2, likeKeyword);
            pstmt.setString(3, likeKeyword);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Book book = resultSetToBook(rs);
                books.add(book);
            }
        } catch (SQLException e) {
            System.err.println("搜索图书失败: " + e.getMessage());
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return books;
    }

    /**
     * 添加新图书
     */
    public boolean addBook(Book book) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "INSERT INTO books (isbn, title, author, publisher, publish_date, " +
                    "price, category_id, location, total_copies, available_copies, status) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, book.getIsbn());
            pstmt.setString(2, book.getTitle());
            pstmt.setString(3, book.getAuthor());
            pstmt.setString(4, book.getPublisher());
            // 使用完整的限定名 java.sql.Date
            pstmt.setDate(5, java.sql.Date.valueOf(book.getPublishDate()));
            pstmt.setDouble(6, book.getPrice());
            pstmt.setString(7, book.getCategory());
            pstmt.setString(8, book.getLocation());
            pstmt.setInt(9, book.getTotalCopies());
            pstmt.setInt(10, book.getAvailableCopies());
            pstmt.setString(11, book.getStatus().name());

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            System.err.println("添加图书失败: " + e.getMessage());
            return false;
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
    }

    /**
     * 更新图书信息
     */
    public boolean updateBook(Book book) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "UPDATE books SET title=?, author=?, publisher=?, publish_date=?, " +
                    "price=?, category_id=?, location=?, total_copies=?, available_copies=?, " +
                    "status=? WHERE isbn=?";

            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, book.getTitle());
            pstmt.setString(2, book.getAuthor());
            pstmt.setString(3, book.getPublisher());
            // 使用完整的限定名 java.sql.Date
            pstmt.setDate(4, java.sql.Date.valueOf(book.getPublishDate()));
            pstmt.setDouble(5, book.getPrice());
            pstmt.setString(6, book.getCategory());
            pstmt.setString(7, book.getLocation());
            pstmt.setInt(8, book.getTotalCopies());
            pstmt.setInt(9, book.getAvailableCopies());
            pstmt.setString(10, book.getStatus().name());
            pstmt.setString(11, book.getIsbn());

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            System.err.println("更新图书失败: " + e.getMessage());
            return false;
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
    }

    /**
     * 删除图书
     */
    public boolean deleteBook(String isbn) {
        Connection conn = null;
        PreparedStatement pstmt = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "DELETE FROM books WHERE isbn = ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, isbn);

            int result = pstmt.executeUpdate();
            return result > 0;

        } catch (SQLException e) {
            System.err.println("删除图书失败: " + e.getMessage());
            return false;
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
    }

    /**
     * 获取图书分类统计
     */
    public Map<String, Integer> getBooksByCategory() {
        Map<String, Integer> categoryCount = new HashMap<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT category_id, COUNT(*) as count FROM books GROUP BY category_id";
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                categoryCount.put(rs.getString("category_id"), rs.getInt("count"));
            }
        } catch (SQLException e) {
            System.err.println("获取图书分类统计失败: " + e.getMessage());
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return categoryCount;
    }

    /**
     * 获取热门图书（借阅次数最多的）
     */
    public List<Book> getPopularBooks(int limit) {
        List<Book> books = new ArrayList<>();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            String sql = "SELECT b.*, (b.total_copies - b.available_copies) as borrowed_count " +
                    "FROM books b ORDER BY borrowed_count DESC LIMIT ?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, limit);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Book book = resultSetToBook(rs);
                books.add(book);
            }
        } catch (SQLException e) {
            System.err.println("获取热门图书失败: " + e.getMessage());
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        return books;
    }

    /**
     * 将ResultSet转换为Book对象
     */
    private Book resultSetToBook(ResultSet rs) throws SQLException {
        Book book = new Book(
                rs.getString("isbn"),
                rs.getString("title"),
                rs.getString("author"),
                rs.getString("publisher"),
                // 使用 java.sql.Date 转换为 LocalDate
                rs.getDate("publish_date").toLocalDate(),
                rs.getDouble("price"),
                rs.getString("category_id"),
                rs.getString("location"),
                rs.getInt("total_copies")
        );
        book.setAvailableCopies(rs.getInt("available_copies"));

        String status = rs.getString("status");
        if (status != null) {
            try {
                book.setStatus(Book.BookStatus.valueOf(status));
            } catch (IllegalArgumentException e) {
                book.setStatus(Book.BookStatus.AVAILABLE);
            }
        }

        return book;
    }
}